package com.yulian.web_api.report;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;
import java.util.stream.Collectors;

import com.healthmarketscience.sqlbuilder.*;
import com.yulian.repository.CommonRepositoryImpl;
import com.yulian.util.CommonUtils;
import com.yulian.util.DataUtils;

import com.yulian.util.TimeUtils;
import com.yulian.web_api.vo.GoodsGrossProfitCommissionDetailVo;
import io.netty.util.internal.logging.InternalLogger;
import io.netty.util.internal.logging.Log4JLoggerFactory;
import io.vertx.core.CompositeFuture;
import io.vertx.core.Future;
import io.vertx.core.Promise;
import io.vertx.core.json.JsonArray;
import io.vertx.core.json.JsonObject;
import io.vertx.pgclient.PgPool;
import io.vertx.sqlclient.Tuple;

/**
 * 该类位于业务逻辑层，主要实现商品毛利及提成管理报表数据查询，新增数据，更新数据，删除数据、导出、打印数据的业务逻辑，为MainVerticle中的REST接口提供服务
 *
 * @author
 */
public class GoodsGrossProfitCommissionDetailManager {
    final InternalLogger logger = Log4JLoggerFactory.getInstance(GoodsGrossProfitCommissionDetailManager.class);
    CommonRepositoryImpl commonRepository = new CommonRepositoryImpl();

    /**
     * 根据传入的查询参数获取满足条件的记录
     *
     * @param client
     * @param config
     * @param bodyJSObj
     * @return
     */
    public Future<JsonObject> getRecords(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        //最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        // 验证必须传递的字段
        List<String> must_fields = Arrays.asList( "big_area_id_list", "site_id_list", "warehouse_id_list", "goods_gather_area_id_list", "supplier_id_list", "page_size", "page_index");
        JsonObject must = this.commonRepository.verifyAllParamMust(bodyJSObj, must_fields);
        if (must != null) {
            promise.complete(must);
            return promise.future();
        }
        //从bodyJson中获取数据
//        String startTimeParam = bodyJSObj.getString("startTimeParam");
//        String endTimeParam = bodyJSObj.getString("endTimeParam");
        String big_area_id_list = bodyJSObj.getString("big_area_id_list");
        String site_id_list = bodyJSObj.getString("site_id_list");
        String warehouse_id_list = bodyJSObj.getString("warehouse_id_list");
        String goods_gather_area_id_list = bodyJSObj.getString("goods_gather_area_id_list");
        String supplier_id_list = bodyJSObj.getString("supplier_id_list");
        String type = bodyJSObj.getString("type"); //1 供应链合伙人 2销售合伙人 3加工中心管理人
        String name = bodyJSObj.getString("name");
        List<String> list =new ArrayList<>();
        if (!type.equals("") &&  !type.equals("null") && type!=null){
             list = List.of(type.split(","));
        }
//        String supplier_pickup_goods_inspect_accept_id_list = bodyJSObj.getString("supplier_pickup_goods_inspect_accept_id_list");

        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;
        //数据是否为空校验
        if (DataUtils.isEmpty(big_area_id_list, site_id_list, goods_gather_area_id_list, warehouse_id_list, supplier_id_list, page_size, page_index)) {
            promise.fail(CommonUtils.createExceptionJsonObjectStr(408, "参数 不合法"));
            return promise.future();
        }
        //用于传递给构建sql的JsonObject
        JsonObject queryJSObj = new JsonObject();
        queryJSObj.put("big_area_id", big_area_id_list);
        queryJSObj.put("site_id", site_id_list);
        queryJSObj.put("warehouse_id", warehouse_id_list);
        queryJSObj.put("goods_gather_area_id", goods_gather_area_id_list);
        queryJSObj.put("supplier_id", supplier_id_list);
//        queryJSObj.put("supplier_pickup_goods_inspect_accept_id", supplier_pickup_goods_inspect_accept_id_list);

        if (list.size()==2){
            //有需要过滤的查询合伙人
            if (list.get(0).equals("1") || list.get(1).equals("1")){
                queryJSObj.put("partner_name", name);
                if (list.get(0).equals("2")||list.get(1).equals("2")){
                    queryJSObj.put("gross_profit_commission_processing_center_manager", name);
                }
            }else if (list.get(0).equals("2") || list.get(1).equals("2")){
                queryJSObj.put("gross_profit_commission_processing_center_manager", name);
            }
        }else if (list.size()==1){
            if (list.get(0).equals("1")){
                queryJSObj.put("partner_name", name);
            }else if (list.get(0).equals("2")){
                queryJSObj.put("gross_profit_commission_processing_center_manager", name);
            }
        }
        this.commonRepository.getCon(client).onSuccess(conn -> {
            //用于传递给构建sql的JsonObject
            String sql = createSelectSql(queryJSObj, query_offset, query_limit);
            //获取满足条件的记录总数
            String count_sql = this.commonRepository.createCountSql(queryJSObj, "goods_gross_profit_commission_detail");
            Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "getRecordsGoodsGrossProfitCommissionDetailTotal");
            Future<JsonArray> future =  this.commonRepository.queryJsonArray(conn, sql, "getRecordsGoodsGrossProfitCommissionDetailArray");
            CompositeFuture.join(Arrays.asList(getTotalFuture, future )).onComplete(ar2 -> {
                if (ar2.succeeded()) {
                    int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                    JsonObject dataJSObj = new JsonObject();
                    JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(1);
                    List<GoodsGrossProfitCommissionDetailVo> vos =new ArrayList<>();
                    for (int i = 0 ; i < recordsJSArray.size();i++){
                        JsonObject JsonObject = recordsJSArray.getJsonObject(i);
                        GoodsGrossProfitCommissionDetailVo vo =new GoodsGrossProfitCommissionDetailVo();
                        //斤价
                        double land_price = Double.parseDouble(JsonObject.getString("land_price")==null? "0":JsonObject.getString("land_price"));
                        BigDecimal bigDecimal01= new BigDecimal(land_price);
                        //销售退货入库总重量
                        double retrun_warehouse_weight = Double.parseDouble(JsonObject.getString("retrun_warehouse_weight")==null? "0":JsonObject.getString("retrun_warehouse_weight"));
                        BigDecimal bigDecimal1= new BigDecimal(retrun_warehouse_weight);
                        //销售退货入库单总金额
                        double retrun_warehouse_amount = bigDecimal01.multiply(bigDecimal1).doubleValue();
                        JsonObject.put("retrun_warehouse_amount",retrun_warehouse_amount);
                        //商品报损总重量
                        double loss_weight = Double.parseDouble(JsonObject.getString("loss_weight")==null? "0":JsonObject.getString("loss_weight"));
                        BigDecimal bigDecimal001= new BigDecimal(loss_weight);
                        //商品报损总金额
                        double loss_amount = bigDecimal01.multiply(bigDecimal001).doubleValue();
                        JsonObject.put("loss_amount",loss_amount);
                        //盘盈盘亏总重量
                        double profit_loss_weight = Double.parseDouble(JsonObject.getString("profit_loss_weight")==null? "0":JsonObject.getString("profit_loss_weight"));
                        BigDecimal bigDecimal51= new BigDecimal(profit_loss_weight);
                        //盘盈盘亏总金额
                        double profit_loss_amount = bigDecimal01.multiply(bigDecimal51).doubleValue();
                        JsonObject.put("profit_loss_amount",profit_loss_amount);
                        //问题商品供应商赔偿金额
                        double unqualify_compensation_affirm_amount= Double.parseDouble(JsonObject.getString("unqualify_compensation_affirm_amount")==null? "0":JsonObject.getString("unqualify_compensation_affirm_amount"));
                        double unqualify_compensation_affirm_amount2= Double.parseDouble(JsonObject.getString("unqualify_compensation_affirm_amount2")==null? "0":JsonObject.getString("unqualify_compensation_affirm_amount2"));
                        BigDecimal bigDecimal3= new BigDecimal(unqualify_compensation_affirm_amount);
                        BigDecimal bigDecimal4= new BigDecimal(unqualify_compensation_affirm_amount2);
                        //问题商品供应商赔偿金额 总额
                        double unqualify_compensation_affirm_amount3 = bigDecimal3.add(bigDecimal4).doubleValue();
                        JsonObject.put("unqualify_compensation_affirm_amount3",unqualify_compensation_affirm_amount3);
                        //采购退货重量
                        double purchase_return_weight = Double.parseDouble(JsonObject.getString("purchase_return_weight")==null? "0":JsonObject.getString("purchase_return_weight"));
                        BigDecimal bigDecimal5= new BigDecimal(purchase_return_weight);
                        //采购退货总金额
                        double purchase_return_amount=bigDecimal01.multiply(bigDecimal5).doubleValue();
                        JsonObject.put("purchase_return_amount",purchase_return_amount);
                        //订单可售量总重量
                            //订单件数
                            double order_number = Double.parseDouble(JsonObject.getString("order_number")==null? "0":JsonObject.getString("order_number"));
                            BigDecimal bigDecimal7= new BigDecimal(order_number);
                            //商品可售量包规
                            double sales_package_spec = Double.parseDouble(JsonObject.getString("sales_package_spec")==null? "0":JsonObject.getString("sales_package_spec"));
                            BigDecimal bigDecimal8= new BigDecimal(sales_package_spec);
                        double order_sales_weight = bigDecimal7.multiply(bigDecimal8).doubleValue();
                        BigDecimal bigDecimal9=new BigDecimal(order_sales_weight);
                        JsonObject.put("order_sales_weight",order_sales_weight);
                        //销售净重
                        double supply_chain_goods_algorithm_package_spec = Double.parseDouble(JsonObject.getString("supply_chain_goods_algorithm_package_spec")==null? "0":JsonObject.getString("supply_chain_goods_algorithm_package_spec"));
                        BigDecimal bigDecimal10= new BigDecimal(supply_chain_goods_algorithm_package_spec);
                       double sales_weight =  bigDecimal10.multiply(bigDecimal7).doubleValue();
                        JsonObject.put("sales_weight",sales_weight);
                        //配送订单总金额
                        double order_weight = Double.parseDouble(JsonObject.getString("order_weight")==null? "0":JsonObject.getString("order_weight"));
                        BigDecimal bigDecimal30= new BigDecimal(order_weight);
                        double order_amount =  bigDecimal01.multiply(bigDecimal30).doubleValue();
                        JsonObject.put("order_amount",order_amount);
                        //批次商品在线库存总重量余额
                            //入库验收总重量
                            double inspect_accept_total_weight = Double.parseDouble(JsonObject.getString("inspect_accept_total_weight")==null? "0":JsonObject.getString("inspect_accept_total_weight"));
                            BigDecimal bigDecimal6= new BigDecimal(inspect_accept_total_weight);
                        double online_inventory_all_weight =   bigDecimal6.subtract(bigDecimal001).add(bigDecimal1).add(bigDecimal51).subtract(bigDecimal9).doubleValue();
                        JsonObject.put("online_inventory_all_weight",online_inventory_all_weight);
                        //商品订单总毛利
                            //订单商品供应商采购成本斤价
                            double settlement_price1 = Double.parseDouble(JsonObject.getString("settlement_price1")==null? "0":JsonObject.getString("settlement_price1"));
                            BigDecimal bigDecimal12= new BigDecimal(settlement_price1);
                            double settlement_price2 = Double.parseDouble(JsonObject.getString("settlement_price2")==null? "0":JsonObject.getString("settlement_price2"));
                            BigDecimal bigDecimal13= new BigDecimal(settlement_price2);
                            //订单商品干线运费斤价
                            double a = Double.parseDouble(JsonObject.getString("sales_real_transport_price")==null? "0":JsonObject.getString("sales_real_transport_price"));
                            BigDecimal bigDecimala= new BigDecimal(a);
                            //订单商品到岸成本斤价
                            double cost_per_kilogram =bigDecimal12.add(bigDecimal13).add(bigDecimala).doubleValue();
                            BigDecimal bigDecimalCostPerKilogram= new BigDecimal(cost_per_kilogram);
                            //订单商品挂牌斤价
                            double member_price1 = Double.parseDouble(JsonObject.getString("member_price1")==null? "0":JsonObject.getString("member_price1"));
                            BigDecimal bigDecimal11= new BigDecimal(member_price1);
                            //订单商品挂牌包规
                            double list_package_spec = Double.parseDouble(JsonObject.getString("list_package_spec")==null? "0":JsonObject.getString("list_package_spec"));
                            BigDecimal bigDecimal14= new BigDecimal(list_package_spec);
                            //订单商品按斤到岸毛利
                            BigDecimal bigDecimalb =bigDecimalCostPerKilogram.multiply(bigDecimal8).divide(bigDecimal14,4, RoundingMode.DOWN);
                            BigDecimal bigDecimalLandedGrossProfit = bigDecimal11.subtract(bigDecimalb);
                            //商品订单按件毛利
                            BigDecimal bigDecimalc= bigDecimalLandedGrossProfit.multiply(bigDecimal14);

                        double all_amount = bigDecimalc.multiply(bigDecimal7).doubleValue();
                        JsonObject.put("all_amount",all_amount);

                        //在线可售库存结余总重量

//                        BigDecimal bigDecimal21= new BigDecimal();

                        //批次商品总毛利
                            BigDecimal bigDecimal15 = new BigDecimal(loss_amount);
                            BigDecimal bigDecimal16 = new BigDecimal(profit_loss_amount);
                            BigDecimal bigDecimal17 = new BigDecimal(unqualify_compensation_affirm_amount3);
                            BigDecimal bigDecimal23 = new BigDecimal(online_inventory_all_weight);
                            //商品折损供应商补贴金额
                            double good_damage_supplier_subsidy_price = Double.parseDouble(JsonObject.getString("good_damage_supplier_subsidy_price")==null? "0":JsonObject.getString("good_damage_supplier_subsidy_price"));
                            BigDecimal bigDecimal18= new BigDecimal(good_damage_supplier_subsidy_price);
                            //商品内部赔偿金额
                            double indoor_subsidy_price = Double.parseDouble(JsonObject.getString("indoor_subsidy_price")==null? "0":JsonObject.getString("indoor_subsidy_price"));
                            BigDecimal bigDecimal19= new BigDecimal(indoor_subsidy_price);
                            //销售投诉赔偿金额
                            double sales_complaints_subsidy_price = Double.parseDouble(JsonObject.getString("sales_complaints_subsidy_price")==null? "0":JsonObject.getString("sales_complaints_subsidy_price"));
                            BigDecimal bigDecimal20= new BigDecimal(sales_complaints_subsidy_price);

                            BigDecimal bigDecimale = bigDecimalCostPerKilogram.multiply(bigDecimal23);
                            BigDecimal bigDecimald = bigDecimalc.multiply(bigDecimal7).subtract(bigDecimal15).subtract(bigDecimal16).add(bigDecimal17).add(bigDecimal18).add(bigDecimal19).subtract(bigDecimal20);
                        double total_gross_profit_of_batch_products =  bigDecimale.add(bigDecimald).doubleValue();
                        BigDecimal bigDecimal22 = new BigDecimal(total_gross_profit_of_batch_products);
                        JsonObject.put("total_gross_profit_of_batch_products",total_gross_profit_of_batch_products);

                        //供应链合伙人毛利提成比例
                        double supply_partner_commission_ratio = Double.parseDouble(JsonObject.getString("supply_partner_commission_ratio")==null? "0":JsonObject.getString("supply_partner_commission_ratio"));
                        BigDecimal bigDecimal24= new BigDecimal(supply_partner_commission_ratio);
                        //加工中心管理人毛利提成比例
                        double processing_manager_commission_ratio = Double.parseDouble(JsonObject.getString("processing_manager_commission_ratio")==null? "0":JsonObject.getString("processing_manager_commission_ratio"));
                        BigDecimal bigDecimal25= new BigDecimal(processing_manager_commission_ratio);
                        //销售合伙人毛利提成比例
                        double sale_partner_commission_ratio = Double.parseDouble(JsonObject.getString("sale_partner_commission_ratio")==null? "0":JsonObject.getString("sale_partner_commission_ratio"));
                        BigDecimal bigDecimal26= new BigDecimal(sale_partner_commission_ratio);
                        //供应链合伙人提成
                        double supply_partner_ticheng = bigDecimal22.multiply(bigDecimal24).doubleValue();
                        JsonObject.put("supply_partner_ticheng",supply_partner_ticheng);
                        //加工中心管理人提成
                        double processing_manager_ticheng = bigDecimal22.multiply(bigDecimal25).doubleValue();
                        JsonObject.put("processing_manager_ticheng",processing_manager_ticheng);
                        //供应链合伙人分摊报损金额
                        double partner_allocation_loss_amount1 = Double.parseDouble(JsonObject.getString("partner_allocation_loss_amount1")==null? "0":JsonObject.getString("partner_allocation_loss_amount1"));
                        BigDecimal bigDecimal40= new BigDecimal(partner_allocation_loss_amount1);
                        double partner_allocation_loss_amount2 = Double.parseDouble(JsonObject.getString("partner_allocation_loss_amount2")==null? "0":JsonObject.getString("partner_allocation_loss_amount2"));
                        BigDecimal bigDecimal41= new BigDecimal(partner_allocation_loss_amount2);
                        double a1 = bigDecimal40.add(bigDecimal41).doubleValue();
                        JsonObject.put("partner_allocation_loss_amount_all",a1);
                        //销售合伙人分摊报损金额
                        double sales_partner_allocation_loss_amount1 = Double.parseDouble(JsonObject.getString("sales_partner_allocation_loss_amount1")==null? "0":JsonObject.getString("sales_partner_allocation_loss_amount1"));
                        BigDecimal bigDecimal42= new BigDecimal(sales_partner_allocation_loss_amount1);
                        double sales_partner_allocation_loss_amount2 = Double.parseDouble(JsonObject.getString("sales_partner_allocation_loss_amount2")==null? "0":JsonObject.getString("sales_partner_allocation_loss_amount2"));
                        BigDecimal bigDecimal43= new BigDecimal(sales_partner_allocation_loss_amount2);
                        double a2 = bigDecimal42.add(bigDecimal43).doubleValue();
                        JsonObject.put("sales_partner_allocation_loss_amount_all",a2);

                        //实体类
                        vo.setBig_area_name(JsonObject.getString("big_area_name"));
                        vo.setTotal_gross_profit_of_batch_products(Double.parseDouble(JsonObject.getString("total_gross_profit_of_batch_products")));
                        vo.setSupply_partner_ticheng(Double.parseDouble(JsonObject.getString("supply_partner_ticheng")));
                        vo.setSite_name(JsonObject.getString("site_name"));
                        vo.setWarehouse_name(JsonObject.getString("warehouse_name"));
                        vo.setWarehouse_id(Integer.parseInt(JsonObject.getString("warehouse_id")));
                        vo.setGoods_gather_area_name(JsonObject.getString("goods_gather_area_name"));
                        vo.setAggregation_name(JsonObject.getString("aggregation_name"));
                        vo.setAggregation_id(Integer.parseInt(JsonObject.getString("aggregation_id")));
                        vo.setId(Integer.parseInt(JsonObject.getString("id")));
                        vo.setType_name(JsonObject.getString("type_name"));
                        vo.setList_package_spec(Double.parseDouble(JsonObject.getString("list_package_spec")==null? "0":JsonObject.getString("list_package_spec")));
                        vo.setSales_package_spec(Double.parseDouble(JsonObject.getString("sales_package_spec")==null? "0":JsonObject.getString("sales_package_spec")));
                        vo.setSupply_chain_goods_settle_package_spec(Double.parseDouble(JsonObject.getString("supply_chain_goods_settle_package_spec")==null? "0":JsonObject.getString("supply_chain_goods_settle_package_spec")));
                        vo.setSupply_chain_goods_algorithm_package_spec(Double.parseDouble(JsonObject.getString("supply_chain_goods_algorithm_package_spec")==null? "0":JsonObject.getString("supply_chain_goods_algorithm_package_spec")));
                        vo.setTime_line(JsonObject.getString("time_line"));
                        vo.setSupply_chain_goods_name(JsonObject.getString("supply_chain_goods_name"));
                        vo.setSupplier_name(JsonObject.getString("supplier_name"));
                        vo.setPartner_name(JsonObject.getString("partner_name"));
                        vo.setGross_profit_commission_processing_center_manager(JsonObject.getString("gross_profit_commission_processing_center_manager"));
                        vo.setSupplier_pickup_goods_inspect_accept_code(JsonObject.getString("supplier_pickup_goods_inspect_accept_code"));
                        vo.setInspect_accept_total_weight(Double.parseDouble(JsonObject.getString("inspect_accept_total_weight")==null? "0":JsonObject.getString("inspect_accept_total_weight")));
                        vo.setRetrun_warehouse_weight(Double.parseDouble(JsonObject.getString("retrun_warehouse_weight")==null? "0":JsonObject.getString("retrun_warehouse_weight")));
                        vo.setRetrun_warehouse_amount(Double.parseDouble(JsonObject.getString("retrun_warehouse_amount")==null? "0":JsonObject.getString("retrun_warehouse_amount")));
                        vo.setOrder_number(Double.parseDouble(JsonObject.getString("order_number")==null? "0":JsonObject.getString("order_number")));
                        vo.setOrder_weight(Double.parseDouble(JsonObject.getString("order_weight")==null? "0":JsonObject.getString("order_weight")));
                        vo.setOrder_amount(Double.parseDouble(JsonObject.getString("order_amount")==null? "0":JsonObject.getString("order_amount")));
                        vo.setSales_weight(Double.parseDouble(JsonObject.getString("sales_weight")==null? "0":JsonObject.getString("sales_weight")));
                        vo.setOrder_sales_weight(Double.parseDouble(JsonObject.getString("order_sales_weight")==null? "0":JsonObject.getString("order_sales_weight")));
                        vo.setAll_amount(Double.parseDouble(JsonObject.getString("all_amount")==null? "0":JsonObject.getString("all_amount")));
                        vo.setLoss_weight(Double.parseDouble(JsonObject.getString("loss_weight")==null? "0":JsonObject.getString("loss_weight")));
                        vo.setLoss_amount(Double.parseDouble(JsonObject.getString("loss_amount")==null? "0":JsonObject.getString("loss_amount")));
                        vo.setProfit_loss_weight(Double.parseDouble(JsonObject.getString("profit_loss_weight")==null? "0":JsonObject.getString("profit_loss_weight")));
                        vo.setProfit_loss_amount(Double.parseDouble(JsonObject.getString("profit_loss_amount")==null? "0":JsonObject.getString("profit_loss_amount")));
                        vo.setGood_damage_supplier_subsidy_code(JsonObject.getString("good_damage_supplier_subsidy_code"));
                        vo.setGoods_batch_code(JsonObject.getString("goods_batch_code"));
                        vo.setUnqualify_compensation_affirm_amount3(Double.parseDouble(JsonObject.getString("unqualify_compensation_affirm_amount3")==null? "0":JsonObject.getString("unqualify_compensation_affirm_amount3")));
                        vo.setGood_damage_supplier_subsidy_price(Double.parseDouble(JsonObject.getString("good_damage_supplier_subsidy_price")==null? "0":JsonObject.getString("good_damage_supplier_subsidy_price")));
                        vo.setIndoor_subsidy_price(Double.parseDouble(JsonObject.getString("indoor_subsidy_price")==null? "0":JsonObject.getString("indoor_subsidy_price")));
                        vo.setPurchase_return_amount(Double.parseDouble(JsonObject.getString("purchase_return_amount")==null? "0":JsonObject.getString("purchase_return_amount")));
                        vo.setSales_complaints_subsidy_price(Double.parseDouble(JsonObject.getString("sales_complaints_subsidy_price")==null? "0":JsonObject.getString("sales_complaints_subsidy_price")));
                        vo.setSales_complaints_subsidy_in_price(Double.parseDouble(JsonObject.getString("sales_complaints_subsidy_in_price")==null? "0":JsonObject.getString("sales_complaints_subsidy_in_price")));
                        vo.setOnline_inventory_all_weight(Double.parseDouble(JsonObject.getString("online_inventory_all_weight")==null? "0":JsonObject.getString("online_inventory_all_weight")));
                        vo.setSupply_partner_commission_ratio(Double.parseDouble(JsonObject.getString("supply_partner_commission_ratio")==null? "0":JsonObject.getString("supply_partner_commission_ratio")));
                        vo.setProcessing_manager_commission_ratio(Double.parseDouble(JsonObject.getString("processing_manager_commission_ratio")==null? "0":JsonObject.getString("processing_manager_commission_ratio")));
                        vo.setSale_partner_commission_ratio(Double.parseDouble(JsonObject.getString("sale_partner_commission_ratio")==null? "0":JsonObject.getString("sale_partner_commission_ratio")));
                        vo.setProcessing_manager_ticheng(Double.parseDouble(JsonObject.getString("processing_manager_ticheng")==null? "0":JsonObject.getString("processing_manager_ticheng")));
                        vo.setSales_partner_commission(Double.parseDouble(JsonObject.getString("sales_partner_commission")==null? "0":JsonObject.getString("sales_partner_commission")));
                        vo.setPartner_allocation_loss_amount_all(Double.parseDouble(JsonObject.getString("partner_allocation_loss_amount_all")==null? "0":JsonObject.getString("partner_allocation_loss_amount_all")));
                        vo.setSales_partner_allocation_loss_amount_all(Double.parseDouble(JsonObject.getString("sales_partner_allocation_loss_amount_all")==null? "0":JsonObject.getString("sales_partner_allocation_loss_amount_all")));
                        vo.setProcessing_manager_allocation_loss_amount(Double.parseDouble(JsonObject.getString("processing_manager_allocation_loss_amount")==null? "0":JsonObject.getString("processing_manager_allocation_loss_amount")));

                        vos.add(vo);
                    }
                    Map<String, List<GoodsGrossProfitCommissionDetailVo>> collect = vos.stream().collect(Collectors.groupingBy(GoodsGrossProfitCommissionDetailVo::getSupply_chain_goods_name));
                    collect.forEach((key, value) -> {
                        Map<Double, List<GoodsGrossProfitCommissionDetailVo>> collect2 = value.stream()
                                .collect(Collectors.groupingBy(GoodsGrossProfitCommissionDetailVo::getSupply_chain_goods_settle_package_spec));
                        collect2.forEach((key2, value2) -> {
                            //同供应链商品名和同结算包规
                            double all = 0.00;
                            for (int i = 0 ; i < value2.size();i++){
                                BigDecimal bigDecimal0 = new BigDecimal(all);
                                BigDecimal bigDecimal1 = new BigDecimal(value2.get(i).getOnline_inventory_all_weight());
                                all = bigDecimal0.add(bigDecimal1).doubleValue();
                            }
                            //统计完成 把值set回对应的地方
                            for (int i = 0 ; i < value2.size();i++){
                               value2.get(i).setOnline_inventory_all_weight_residue(all);
                            }
                        });

                    });
                    dataJSObj.put("total", total_records).put("records",vos);
                    responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                    promise.complete(responseJSObj);
                } else {
                    logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                    promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getRecordsGoodsGrossProfitCommissionDetailTotal", "商品毛利及提成管理明细失败"));
                }
            });
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getRecordsGoodsGrossProfitCommissionDetailTotal", "数据库连接失败")));
        return promise.future();
    }


    /**
     * 创建查询符合搜索条件记录的sql语句
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSql(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("a.id"),new CustomSql("(select h.big_area_name   from big_area h where h.is_delete=0  and h.id=a.big_area_id ) as big_area_name"),
                        new CustomSql("(select h.site_name  from site h   where h.is_delete=0   and h.id=a.site_id ) as site_name"),
                        new CustomSql("warehouse_id"),new CustomSql("(select w.warehouse_name " +
                                "from warehouse w " +
                                "where w.is_delete=0 " +
                                "and w.id=a.warehouse_id limit 1 ) as warehouse_name "),
                        new CustomSql("aggregation_id"),
                        new CustomSql("(select ag.aggregation_name " +
                                "from  aggregation_goods ag " +
                                "where ag.is_delete=0 " +
                                "and id=a.aggregation_id limit 1 ) as aggregation_name"),
                        new CustomSql("(select  (select c.list_package_spec " +
                                "         from goods_parameter_list_goods c " +
                                "         where  c.is_delete=0 " +
                                "           and c.id=gp.goods_parameter_list_goods_id limit 1 ) as list_package_spec " +
                                " from goods_parameter gp " +
                                " where gp.is_delete=0 " +
                                "  and gp.aggregation_id=a.aggregation_id limit 1) as list_package_spec"),
                        new CustomSql("(select         (select c.sales_package_spec " +
                                "                from goods_parameter_list_goods c " +
                                "                where  c.is_delete=0 " +
                                "                  and c.id=gp.goods_parameter_list_goods_id limit 1 ) as sales_package_spec " +
                                " from goods_parameter gp " +
                                " where gp.is_delete=0 " +
                                "  and gp.aggregation_id=a.aggregation_id limit 1 ) as sales_package_spec"),
                        new CustomSql("(select         (select c.supply_chain_goods_algorithm_package_spec " +
                                "                from goods_parameter_supply_chain_goods c " +
                                "                where  c.is_delete=0 " +
                                "                  and c.id=gp.goods_parameter_supply_chain_goods_id limit 1 ) as supply_chain_goods_algorithm_package_spec " +
                                " from goods_parameter gp " +
                                " where gp.is_delete=0 " +
                                "  and gp.aggregation_id=a.aggregation_id limit 1 ) as supply_chain_goods_algorithm_package_spec"),
        new CustomSql("a.goods_gather_area_id"), new CustomSql("(select gga.goods_gather_area_name from goods_gather_area gga  where gga.is_delete=0 and gga.id=a.goods_gather_area_id ) as goods_gather_area_name"),
        new CustomSql("(select (   select  a.type_name  from sales_package_spec_type a  where a.is_delete=0  and a.id =gp.sales_package_spec_type_id  limit 1 ) as type_name " +
                                " from goods_parameter gp  where gp.is_delete=0  and gp.aggregation_id=a.aggregation_id limit 1) as type_name"),
        new CustomSql("( select (   select  a.supply_chain_goods_algorithm_package_spec  from goods_parameter_supply_chain_goods a  where a.is_delete=0  and " +
                "  a.id =gp.goods_parameter_supply_chain_goods_id  limit 1 ) as supply_chain_goods_algorithm_package_spec " +
                "   from goods_parameter gp  where gp.is_delete=0  and gp.aggregation_id=a.aggregation_id limit 1 ) as supply_chain_goods_algorithm_package_spec"),
        new CustomSql("a.aggregation_id"),  new CustomSql("a.supply_chain_goods_id"), new CustomSql("a.supplier_id"),
        new CustomSql("( select scg.supply_chain_goods_name from supply_chain_goods scg where scg.is_delete=0 and scg.id=a.supply_chain_goods_id ) as supply_chain_goods_name"),
        new CustomSql("( select si.supplier_name from supplier_info si where si.is_delete=0 and si.id=a.supplier_id ) as supplier_name"),
        new CustomSql("a.supply_chain_goods_settle_package_spec"), new CustomSql("a.goods_batch_code"), new CustomSql("a.inspect_accept_total_weight"),
        new CustomSql("a.partner_name"),
        new CustomSql("(select sum(srwd.retrun_warehouse_weight) from sales_return_warehouse_detail srwd where srwd.is_delete=0 and srwd.warehouse_id=a.warehouse_id and srwd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and srwd.delivery_date=a.time_line and srwd.supply_chain_goods_id in  " +
                " ( select gp.supply_chain_goods_id from goods_parameter gp  where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id )) as retrun_warehouse_weight"),
//        new CustomSql("(select ibd.land_price from initial_batch_detail ibd where ibd.is_delete=0 and ibd.supply_chain_goods_id in (select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id) and ibd.warehouse_id=a.warehouse_id " +
//          " and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=time_line and ibd.goods_batch_code=a.goods_batch_code )as land_price "),
        new CustomSql("(select ibd.land_price from initial_batch_detail ibd where ibd.is_delete=0 and ibd.supply_chain_goods_id = a.supply_chain_goods_id and ibd.warehouse_id=a.warehouse_id " +
          " and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line and ibd.goods_batch_code=a.goods_batch_code )as land_price "),
                        new CustomSql("a.is_delete"),new CustomSql("a.supplier_pickup_goods_inspect_accept_code"),new CustomSql("a.inspect_accept_total_weight"),
                        new CustomSql("a.time_line"), new CustomSql("a.create_user"), new CustomSql("a.update_user"), new CustomSql("a.create_time"),
                        new CustomSql("a.update_time"),new CustomSql("( select sum(sdpcd.order_number) as order_number  from shift_delivery_plan_completion_detail sdpcd where sdpcd.is_delete=0 and sdpcd.supply_chain_goods_id in (  select gp.supply_chain_goods_id " +
                                "    from goods_parameter gp   where gp.is_delete=0  and gp.aggregation_id=a.aggregation_id) and sdpcd.warehouse_id=a.warehouse_id  and sdpcd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec  and sdpcd.goods_batch_code=a.goods_batch_code " +
                                "  and sdpcd.delivery_date=a.time_line ) as order_number"),new CustomSql("( select sdpcd.order_weight  from shift_delivery_plan_completion_detail sdpcd where sdpcd.is_delete=0 and sdpcd.supply_chain_goods_id in (  select gp.supply_chain_goods_id " +
                                "    from goods_parameter gp   where gp.is_delete=0  and gp.aggregation_id=a.aggregation_id) and sdpcd.warehouse_id=a.warehouse_id  and sdpcd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec  and sdpcd.goods_batch_code=a.goods_batch_code " +
                                "  and sdpcd.delivery_date=a.time_line ) as order_weight"),
                        new CustomSql("(     select sum(sogd.order_gross) asorder_gross  from sales_order_gross_detail sogd  where sogd.is_delete=0  and sogd.supply_chain_goods_id in ( " +
                                "  select gp.supply_chain_goods_id  from goods_parameter gp  where gp.is_delete=0  and gp.aggregation_id=a.aggregation_id)  and sogd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec  and sogd.delivery_date=a.time_line ) as order_gross"),
                        new CustomSql("(select sum(ibd.loss_weight) as loss_weight from initial_batch_detail ibd where ibd.is_delete=0 and ibd.goods_batch_code=a.goods_batch_code and ibd.supply_chain_goods_id in (select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id) and ibd.warehouse_id=a.warehouse_id " +
                                " and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line )as loss_weight "),
                        new CustomSql("(select sum(ibd.profit_loss_weight) as profit_loss_weight from initial_batch_detail ibd where ibd.is_delete=0 and ibd.goods_batch_code=a.goods_batch_code and ibd.supply_chain_goods_id in (select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id) and ibd.warehouse_id=a.warehouse_id " +
                                " and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line )as profit_loss_weight "),
                        new CustomSql("(select gdssd.good_damage_supplier_subsidy_code from good_damage_supplier_subsidy_detail gdssd where gdssd.is_delete=0 and gdssd.goods_batch_code=a.goods_batch_code " +
                                "and gdssd.generate_date=a.time_line and gdssd.warehouse_id=a.warehouse_id and gdssd.real_supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec  ) as good_damage_supplier_subsidy_code"),
                        new CustomSql("(select sum(sc.unqualify_compensation_affirm_amount) as unqualify_compensation_affirm_amount  from supplier_compensation sc  where sc.is_delete=0   and sc.warehouse_id=a.warehouse_id  and sc.supplier_pickup_goods_inspect_accept_detail_id=(   select ibd.sales_supplier_pickup_goods_inspect_accept_id from initial_batch_detail ibd where ibd.is_delete=0 " +
                                "    and ibd.goods_batch_code=a.goods_batch_code  and ibd.warehouse_id=a.warehouse_id  and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line  )   and sc.generate_date=a.time_line " +
                                "  and sc.aggregation_id=a.aggregation_id ) as unqualify_compensation_affirm_amount"),
                        new CustomSql("(select sum(sc.unqualify_compensation_affirm_amount) as unqualify_compensation_affirm_amount  from sales_supplier_compensation sc  where sc.is_delete=0   and sc.warehouse_id=a.warehouse_id " +
                                "  and sc.sales_supplier_pickup_goods_inspect_accept_detail_id=(  select ibd.sales_supplier_pickup_goods_inspect_accept_id from initial_batch_detail ibd where ibd.is_delete=0  and ibd.goods_batch_code=a.goods_batch_code " +
                                "  and ibd.warehouse_id=a.warehouse_id  and ibd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec and ibd.time_line=a.time_line  )  and sc.generate_date=a.time_line  and sc.aggregation_id=a.aggregation_id ) as unqualify_compensation_affirm_amount2"),
                        new CustomSql("(select sum(gdssd.good_damage_supplier_subsidy_price) as good_damage_supplier_subsidy_price from good_damage_supplier_subsidy_detail gdssd where gdssd.is_delete=0 and gdssd.goods_batch_code=a.goods_batch_code " +
                                "and gdssd.generate_date=a.time_line and gdssd.warehouse_id=a.warehouse_id and gdssd.real_supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec  ) as good_damage_supplier_subsidy_price"),
                        new CustomSql("(select sum(gdssd.indoor_subsidy_price) as indoor_subsidy_price from good_indoor_subsidy_detail gdssd where gdssd.is_delete=0 and gdssd.goods_batch_code=a.goods_batch_code " +
                                "and gdssd.generate_date=a.time_line and gdssd.warehouse_id=a.warehouse_id and gdssd.real_supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec  ) as indoor_subsidy_price"),
                        new CustomSql("(select sum(prd.purchase_return_weight) from purchase_return_detail prd where prd.is_delete=0 and prd.warehouse_id=a.warehouse_id and prd.purchase_return_date=a.time_line and prd.goods_batch_code=a.goods_batch_code  " +
                                "  and prd.supply_chain_goods_id in ( select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id )) as purchase_return_weight"),
                        new CustomSql("(select sum(prd.purchase_return_number) from purchase_return_detail prd where prd.is_delete=0 and prd.warehouse_id=a.warehouse_id and prd.purchase_return_date=a.time_line  and prd.goods_batch_code=a.goods_batch_code  " +
                                "  and prd.supply_chain_goods_id in ( select gp.supply_chain_goods_id from goods_parameter gp where gp.is_delete=0 and gp.aggregation_id=a.aggregation_id )) as purchase_return_number"),
                        new CustomSql("(select sum(scsd.sales_complaints_subsidy_price) as sales_complaints_subsidy_price " +
                                "from sales_complaints_subsidy_detail scsd " +
                                "where scsd.is_delete=0 " +
                                "and scsd.warehouse_id=a.warehouse_id " +
                                "and scsd.goods_batch_code=a.goods_batch_code " +
                                "and scsd.trade_date=a.time_line " +
                                "and scsd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec " +
                                "  and scsd.supply_chain_goods_id in " +
                                "      (select gp.supply_chain_goods_id " +
                                "       from goods_parameter gp " +
                                "       where gp.is_delete=0 " +
                                "         and gp.aggregation_id=a.aggregation_id) ) as sales_complaints_subsidy_price"),
                        new CustomSql("(select sum(scsd.sales_complaints_subsidy_in_price) as sales_complaints_subsidy_in_price " +
                                "from sales_complaints_subsidy_detail scsd " +
                                "where scsd.is_delete=0 " +
                                "and scsd.warehouse_id=a.warehouse_id " +
                                "and scsd.goods_batch_code=a.goods_batch_code " +
                                "and scsd.trade_date=a.time_line " +
                                "and scsd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec " +
                                "  and scsd.supply_chain_goods_id in " +
                                "      (select gp.supply_chain_goods_id " +
                                "       from goods_parameter gp " +
                                "       where gp.is_delete=0 " +
                                "         and gp.aggregation_id=a.aggregation_id) ) as sales_complaints_subsidy_in_price"),
                new CustomSql("( select sum(spppd.settlement_price) as settlement_price " +
                        "from supplier_pickup_plan_payment_detail spppd " +
                        "where spppd.is_delete=0 " +
                        "and spppd.id=( " +
                        "    select spgiad.supplier_pickup_plan_payment_detail_id " +
                        "    from supplier_pickup_goods_inspect_accept_detail spgiad " +
                        "    where spgiad.is_delete=0 " +
                        "      and spgiad.goods_batch_code=a.goods_batch_code " +
                        "    ) ) as settlement_price1 "),
                new CustomSql("( select sum(sppc.real_settlement_price) as real_settlement_price " +
                        "from sales_pickup_plan_command sppc " +
                        "where sppc.is_delete=0 " +
                        "  and sppc.id=( " +
                        "    select sspgiad.sales_pickup_plan_command_id " +
                        "    from sales_supplier_pickup_goods_inspect_accept_detail sspgiad " +
                        "    where sspgiad.is_delete=0 " +
                        "      and sspgiad.goods_batch_code=a.goods_batch_code " +
                        ") ) as settlement_price2 "),
                new CustomSql("( select sum(mt.sales_real_transport_price) as sales_real_transport_price  from mainline_transport mt  where mt.is_delete=0  and mt.id= ( " +
                        "    select bpplcd.batch_pickup_plan_load_completion_id  from batch_pickup_plan_load_completion_detail bpplcd  where bpplcd.is_delete=0   and bpplcd.id=( " +
                        "     select spgiad.batch_pickup_plan_load_completion_detail_id  from supplier_pickup_goods_inspect_accept_detail spgiad  left join supplier_pickup_goods_inspect_accept spgia " +
                        "     on spgiad.supplier_pickup_goods_inspect_accept_id = spgia.id " +
                        "        where spgiad.id=a.supplier_pickup_goods_inspect_accept_detail_id  and spgia.supplier_pickup_goods_inspect_accept_code=a.supplier_pickup_goods_inspect_accept_code " +
                        "    ))  and mt.site_id=a.site_id ) as sales_real_transport_price "),
                new CustomSql(" (select sum(gpcr.supply_partner_commission_ratio) as supply_partner_commission_ratio  from gather_partner_commission_ratio gpcr  where gpcr.is_delete=0 " +
                        "and gpcr.site_id=a.site_id  and gpcr.class_lv1_id=a.class_lv1_id  and gpcr.gross_margin_max >= a.cif_gross_profit_margin  and gpcr.gross_margin_min < a.cif_gross_profit_margin ) as supply_partner_commission_ratio "),
                        new CustomSql(" (select sum(gpcr.processing_manager_commission_ratio) as processing_manager_commission_ratio  from gather_partner_commission_ratio gpcr  where gpcr.is_delete=0 " +
                                " and gpcr.site_id=a.site_id  and gpcr.class_lv1_id=a.class_lv1_id  and gpcr.gross_margin_max >= a.cif_gross_profit_margin and gpcr.gross_margin_min < a.cif_gross_profit_margin ) as processing_manager_commission_ratio "),
                        new CustomSql(" (select sum(gpcr.sale_partner_commission_ratio) as sale_partner_commission_ratio  from gather_partner_commission_ratio gpcr  where gpcr.is_delete=0 " +
                                " and gpcr.site_id=a.site_id and gpcr.class_lv1_id=a.class_lv1_id  and gpcr.gross_margin_max >=  a.cif_gross_profit_margin and gpcr.gross_margin_min < a.cif_gross_profit_margin ) as sale_partner_commission_ratio "),
                new CustomSql("( select sum(sogd.sales_partner_commission) as sales_partner_commission " +
                        "from sales_order_gross_detail sogd " +
                        "where sogd.is_delete=0 " +
                        "and sogd.supply_chain_goods_id=a.supply_chain_goods_id " +
                        "and sogd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec ) as sales_partner_commission"),
                new CustomSql("( select sum(gpala.partner_allocation_loss_amount) as partner_allocation_loss_amount  " +
                        "from gather_partner_allocation_loss_amount gpala " +
                        "where gpala.is_delete=0 " +
                        "and gpala.partner_id=( " +
                        "    select si.partner_id from supplier_info si where si.is_delete=0 and si.id=a.supplier_id " +
                        "    ) ) as partner_allocation_loss_amount1"),
                new CustomSql("( select sum(pala.partner_allocation_loss_amount) as partner_allocation_loss_amount  " +
                        "from partner_allocation_loss_amount pala " +
                        "where pala.is_delete=0 " +
                        "  and pala.partner_id=( " +
                        "    select si.partner_id from supplier_info si where si.is_delete=0 and si.id=a.supplier_id " +
                        ") ) as partner_allocation_loss_amount2"),
                new CustomSql("a.gross_profit_commission_processing_center_manager"),
                new CustomSql("( select sum(gpmala.processing_manager_allocation_loss_amount) as processing_manager_allocation_loss_amount  from gather_processing_manager_allocation_loss_amount gpmala " +
                        " where gpmala.is_delete=0  and gpmala.processing_manager_name =(  select rppna.gross_profit_commission_processing_center_manager " +
                        "   from real_pickup_plan_number_allocation rppna   where rppna.is_delete=0  and rppna.id=( " +
                        "   select spgiad.real_pickup_plan_number_allocation_id  from supplier_pickup_goods_inspect_accept_detail spgiad   " +
                        "  left join supplier_pickup_goods_inspect_accept spgia  on spgiad.supplier_pickup_goods_inspect_accept_id = spgia.id " +
                        "        where spgiad.id=a.supplier_pickup_goods_inspect_accept_detail_id  and spgia.supplier_pickup_goods_inspect_accept_code=a.supplier_pickup_goods_inspect_accept_code  ))) as processing_manager_allocation_loss_amount "),
                new CustomSql("( select sum(gspala.sales_partner_allocation_loss_amount) as sales_partner_allocation_loss_amount " +
                        "from gather_sales_partner_allocation_loss_amount gspala " +
                        "where gspala.is_delete=0 " +
                        "and gspala.sales_partner_name= ( " +
                        "    select sogd.sales_partner_name " +
                        "    from sales_order_gross_detail sogd " +
                        "    where sogd.is_delete=0 " +
                        "      and sogd.supply_chain_goods_id=a.supply_chain_goods_id " +
                        "      and sogd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec " +
                        "    ) ) as sales_partner_allocation_loss_amount1"),
                new CustomSql("( select sum(spala.sales_partner_allocation_loss_amount) as sales_partner_allocation_loss_amount " +
                        "from  sales_partner_allocation_loss_amount  spala " +
                        "where spala.is_delete=0 " +
                        "and spala.sales_partner_name=( " +
                        "    select sogd.sales_partner_name " +
                        "    from sales_order_gross_detail sogd " +
                        "    where sogd.is_delete=0 " +
                        "      and sogd.supply_chain_goods_id=a.supply_chain_goods_id " +
                        "      and sogd.supply_chain_goods_settle_package_spec=a.supply_chain_goods_settle_package_spec " +
                        "    ) ) as sales_partner_allocation_loss_amount2 "),
                new CustomSql("( select lp.member_price1 " +
                        "from list_price lp " +
                        "where lp.is_delete=0 " +
                        "and lp.aggregation_id=a.aggregation_id limit 1 ) as member_price1")
                        )
        .addCustomFromTable("goods_gross_profit_commission_detail a");
//        sq.getWhereClause().addCustomCondition(Conditions.greaterThanOrEq(new CustomSql("create_time"), TimeUtils.parseLocalDate(startTimeParam, "yyyy-MM-dd HH:mm:ss")));
//        sq.getWhereClause().addCustomCondition(Conditions.lessThanOrEq(new CustomSql("create_time"), TimeUtils.parseLocalDate(endTimeParam, "yyyy-MM-dd HH:mm:ss")));
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if ("start_trade_date".equals(key)) {
                    sq.getWhereClause().addCustomCondition(Conditions.greaterThanOrEq(new CustomSql("trade_date"),
                    TimeUtils.parseLocalDate(value, "yyyy-MM-dd")).setDisableParens(true));
                    continue;
                } else if ("end_trade_date".equals(key)) {
                    sq.getWhereClause().addCustomCondition(Conditions.lessThanOrEq(new CustomSql("trade_date"),
                    TimeUtils.parseLocalDate(value, "yyyy-MM-dd")).setDisableParens(true));
                    continue;
                }
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where InCondtion子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }

    /**
     * 查看销售退库入库单明细 根据传入的查询参数获取满足条件的记录
     *
     * @param client
     * @param config
     * @param bodyJSObj
     * @return
     */
    public Future<JsonObject> getRecordsForSalesReturnWarehouseDetial(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        //最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        // 验证必须传的数值字段
        List<String> numeric_fields = Arrays.asList("warehouse_id", "aggregation_id", "supply_chain_goods_settle_package_spec", "page_size", "page_index","goods_batch_code");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }

        //从bodyJson中获取数据   各字段注释请参看数据库设计文档中数据表supplier_batch_goods_psi的说明\
        String warehouse_id = bodyJSObj.getString("warehouse_id");
        String aggregation_id = bodyJSObj.getString("aggregation_id");
        String supply_chain_goods_settle_package_spec = bodyJSObj.getString("supply_chain_goods_settle_package_spec");
        String goods_batch_code = bodyJSObj.getString("goods_batch_code");
        String delivery_date = bodyJSObj.getString("delivery_date");

        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;

        JsonObject queryJSObj = new JsonObject();

        //首先根据聚合商品名获取对应的供应链商品名
        Set<Integer> supplys= new HashSet<>();
        this.commonRepository.getCon(client).onSuccess(conn -> {
                    //先根据聚合商品名获取对应的供应链商品名
                    JsonObject queryJSObj1 = new JsonObject();
                    queryJSObj1.put("aggregation_id", aggregation_id);
                    String get_supply_chain_gooods_id_sql = createSelectSqlGetSupplyChainGooodsId(queryJSObj1, query_offset, query_limit);
                    this.commonRepository.queryJsonArray(conn, get_supply_chain_gooods_id_sql, "getInitialBatchDetialRecords")
                            .onSuccess(resJSArray1 -> {
                                //        取出每条记录中含code字段的值放入对应的集合
                                if (resJSArray1.size()==0){
                                    JsonObject dataJSObj = new JsonObject();
                                    dataJSObj.put("total", 0).put("records","");
                                    responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                                    promise.complete(responseJSObj);
                                }else {
                                    for (int i = 0; i < resJSArray1.size(); i++) {
                                        JsonObject jsonObj = resJSArray1.getJsonObject(i);
                                        Integer a = Integer.valueOf(jsonObj.getString("supply_chain_goods_id"));
                                        supplys.add(a);
                                    }
                                    //用于传递给构建sql的JsonObject
                                    queryJSObj.put("warehouse_id", warehouse_id);
                                    String supply_str="";
                                    Integer[] array = supplys.toArray(new Integer[supplys.size()]);
                                    for (int i = 0 ; i < array.length ; i ++){
                                        if (i==0){
                                            supply_str=supply_str+array[i];
                                        }else {
                                            supply_str=supply_str+","+array[i];
                                        }
                                    }
                                    queryJSObj.put("supply_chain_goods_id", supply_str);
                                    queryJSObj.put("supply_chain_goods_settle_package_spec", supply_chain_goods_settle_package_spec);
                                    queryJSObj.put("goods_batch_code", goods_batch_code);
                                    queryJSObj.put("is_delete", 0);
                                    queryJSObj.put("delivery_date", delivery_date);

                                    String sql = createSelectSqlForSalesReturnWarehouseDetail(queryJSObj, query_offset, query_limit);

                                    //获取满足条件的记录总数
                                    String count_sql = this.commonRepository.createCountSql(queryJSObj, "sales_return_warehouse_detail");
                                    Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "getRecordsForSalesReturnWarehouseDetial");
                                    Future<JsonArray> future =  this.commonRepository.queryJsonArray(conn, sql, "getRecordsForSalesReturnWarehouseDetial");
                                    CompositeFuture.join(Arrays.asList(getTotalFuture, future )).onComplete(ar2 -> {
                                        if (ar2.succeeded()) {
                                            int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                                            JsonObject dataJSObj = new JsonObject();
                                            JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(1);
                                            dataJSObj.put("total", total_records).put("records",recordsJSArray);
                                            responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                                            promise.complete(responseJSObj);
                                        } else {
                                            logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                                            promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getRecordsForSalesReturnWarehouseDetial", "获取销售退货入库单明细失败"));
                                        }
                                    });
                                }
                            }).onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getRecordsForSalesReturnWarehouseDetial", "获取销售退货入库单明细失败")));
                }).
                onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getRecordsForSalesReturnWarehouseDetial", "数据库连接失败")));
        return promise.future();
    }


    /**
     * 创建查询符合搜索条件记录的sql语句——查看销售退库入库单明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForSalesReturnWarehouseDetail(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("delivery_date"), new CustomSql("shift_delivery_plan_code"), new CustomSql("sales_return_warehouse_code"),
                        new CustomSql("list_goods_id"),new CustomSql("( select lg.list_goods_name " +
                                "from list_goods lg where lg.is_delete=0 and lg.id=list_goods_id ) as list_goods_name"),
                        new CustomSql("list_package_spec"), new CustomSql("sales_package_spec_id"),
                        new CustomSql("(select spst.package_spec_name from sales_package_spec spst where spst.is_delete=0 " +
                                "and spst.id=sales_package_spec_id ) as package_spec_name"),
                        new CustomSql("goods_batch_code"), new CustomSql("supply_chain_goods_id"),
                        new CustomSql("(select scg.supply_chain_goods_name from supply_chain_goods scg  where scg.is_delete=0 " +
                                "and scg.id= supply_chain_goods_id ) as supply_chain_goods_name "),
                        new CustomSql("supply_chain_goods_settle_package_spec"), new CustomSql("buyer_name"), new CustomSql("buyer_id"), new CustomSql("order_code"), new CustomSql("retrun_number"), new CustomSql("warehouse_id"),
                        new CustomSql("retrun_weight"), new CustomSql("retrun_warehouse_weight"), new CustomSql("retrun_warehouse_number"), new CustomSql("return_loss_number"), new CustomSql("return_loss_weight"),
                        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
                .addCustomFromTable("sales_return_warehouse_detail");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }


    /**
     * 根据传入的查询参数获取满足条件的记录——查看销售订单毛利明细
     *
     * @param client    连接池对象
     * @param config    配置对象
     * @param bodyJSObj body参数对象
     * @return 满足条件的记录
     */
    public Future<JsonObject> getRecordsForSalesOrderGrossDetail(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        //从bodyJson中获取传递的参数, 各参数字段含义 请参看 insertOrUpdate方法体中的注释
        /** 从bodyJson中获取传递的参数说明,适用于方法createSelectSql 和 getRecords
         */

        // 验证必须传的数值字段
        List<String> numeric_fields = Arrays.asList("warehouse_id", "aggregation_id", "supply_chain_goods_settle_package_spec", "page_size", "page_index");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }

        //从bodyJson中获取数据   各字段注释请参看数据库设计文档中数据表sales_supplier_goods_profit_loss的说明\
        String warehouse_id = bodyJSObj.getString("warehouse_id");
        String aggregation_id = bodyJSObj.getString("aggregation_id");
        String supply_chain_goods_settle_package_spec = bodyJSObj.getString("supply_chain_goods_settle_package_spec");

        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;

        //首先根据聚合商品名获取对应的供应链商品名
        Set<Integer> supplys= new HashSet<>();
        this.commonRepository.getCon(client).onSuccess(conn -> {
                    //获取满足条件的记录总数
                    //先根据聚合商品名获取对应的供应链商品名
                    JsonObject queryJSObj1 = new JsonObject();
                    queryJSObj1.put("aggregation_id", aggregation_id);
                    String get_supply_chain_gooods_id_sql = createSelectSqlGetSupplyChainGooodsId(queryJSObj1, query_offset, query_limit);
                    this.commonRepository.queryJsonArray(conn, get_supply_chain_gooods_id_sql, "getInitialBatchDetialRecords")
                            .onSuccess(resJSArray1 -> {
                                if (resJSArray1.size()==0){
                                    JsonObject dataJSObj = new JsonObject();
                                    dataJSObj.put("total", 0).put("records","");
                                    responseJSObj.put("id", 0).put("msg", "获取记录成功").put("data", dataJSObj);
                                    promise.complete(responseJSObj);
                                }else {
                                    for (int i = 0; i < resJSArray1.size(); i++) {
                                        JsonObject jsonObj = resJSArray1.getJsonObject(i);
                                        Integer a = Integer.valueOf(jsonObj.getString("supply_chain_goods_id"));
                                        supplys.add(a);
                                    }
                                    // 传递给构建查询sql中where子句的JsonObject,
                                    JsonObject queryJSObj = new JsonObject();
                                    String supply_str="";
                                    Integer[] array = supplys.toArray(new Integer[supplys.size()]);
                                    for (int i = 0 ; i < array.length ; i ++){
                                        if (i==0){
                                            supply_str=supply_str+array[i];
                                        }else {
                                            supply_str=supply_str+","+array[i];
                                        }
                                    }
                                    queryJSObj.put("supply_chain_goods_id", supply_str);
                                    queryJSObj.put("supply_chain_goods_settle_package_spec", supply_chain_goods_settle_package_spec);
                                    queryJSObj.put("is_delete", 0);

                                    String count_sql = this.commonRepository.createCountSql(queryJSObj, "sales_order_gross_detail");
                                    Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "get_sales_order_gross_detail");
                                    String sql = createSelectSqlForSalesOrderGrossDetail(queryJSObj, query_offset, query_limit);
                                    Future<JsonArray> future =  this.commonRepository.queryJsonArray(conn, sql, "getRecordsForSalesOrderGrossDetail");
                                    CompositeFuture.join(Arrays.asList(getTotalFuture,future)).onComplete(ar2 -> {
                                        if (ar2.succeeded()) {
                                            int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                                            JsonObject dataJSObj = new JsonObject();
                                            JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(1);
                                            dataJSObj.put("total", total_records).put("records",recordsJSArray);
                                            responseJSObj.put("id", 0).put("msg", "获取记录成功").put("data", dataJSObj);
                                            promise.complete(responseJSObj);
                                        } else {
                                            logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                                            promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getRecordsForSalesOrderGrossDetail", "获取销售订单毛利明细失败"));
                                        }
                                    });

                                }
                            }).onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getRecordsForSalesReturnWarehouseDetial", "获取供应链商品参数失败")));
            }).onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getRecordsForSalesOrderGrossDetail", "获取销售订单毛利明细失败")));
        return promise.future();
    }

    /**
     * 创建查询符合搜索条件记录的sql语句  查看销售订单毛利明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForSalesOrderGrossDetail(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("site_id"),new CustomSql("(select site_name from site where is_delete=0 and id=site_id ) as site_name "), new CustomSql("delivery_date"),
        new CustomSql("list_goods_id"), new CustomSql("(select lg.list_goods_name from list_goods lg where lg.is_delete=0 and lg.id=list_goods_id ) as list_goods_name "),new CustomSql("list_package_spec"),
        new CustomSql("sales_package_spec_type_id"),new CustomSql("(select spst.type_name from sales_package_spec_type spst where spst.is_delete=0 and spst.id=sales_package_spec_type_id ) type_name "),
        new CustomSql("supply_chain_goods_id"), new CustomSql("(select scg.supply_chain_goods_name from supply_chain_goods scg where scg.is_delete=0 and scg.id=supply_chain_goods_id ) as supply_chain_goods_name "), new CustomSql("supply_chain_goods_settle_package_spec"),
        new CustomSql("sales_partner_name"), new CustomSql("buyer_name"), new CustomSql("buyer_id"), new CustomSql("order_code"),
        new CustomSql("order_number"), new CustomSql("order_weight"), new CustomSql("order_price"), new CustomSql("order_piece_price"),
        new CustomSql("order_amount"), new CustomSql("order_net_weight"), new CustomSql("order_price_by_algorithm_package_spec"), new CustomSql("order_sales_weight"),
        new CustomSql("order_purchase_cost_price"), new CustomSql("order_supplier_freight_price"), new CustomSql("order_land_cost_price"), new CustomSql("order_land_gross_by_jin"),
        new CustomSql("order_gross_by_jian"), new CustomSql("order_gross"), new CustomSql("sales_partner_commission"),
        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
        .addCustomFromTable("sales_order_gross_detail");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }


    /**
     * 根据传入的查询参数获取满足条件的记录——查看商品折损供应商补贴单明细
     *
     * @param client    连接池对象
     * @param config    配置对象
     * @param bodyJSObj body参数对象
     * @return 满足条件的记录
     */
    public Future<JsonObject> getRecordsForGoodDamageSupplierSubsidyDetail(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        //从bodyJson中获取传递的参数, 各参数字段含义 请参看 insertOrUpdate方法体中的注释
        /** 从bodyJson中获取传递的参数说明,适用于方法createSelectSql 和 getRecords
         */

        // 验证必须传的数值字段
        List<String> numeric_fields = Arrays.asList("warehouse_id", "supply_chain_goods_settle_package_spec", "goods_batch_code", "page_size", "page_index","aggregation_id");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }

        //从bodyJson中获取数据   各字段注释请参看数据库设计文档中数据表sales_supplier_goods_profit_loss的说明\
        String warehouse_id = bodyJSObj.getString("warehouse_id");
        String aggregation_id = bodyJSObj.getString("aggregation_id");
        String supply_chain_goods_settle_package_spec = bodyJSObj.getString("supply_chain_goods_settle_package_spec");
        String goods_batch_code = bodyJSObj.getString("goods_batch_code");
        String generate_date = bodyJSObj.getString("generate_date");

        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;
        // 传递给构建查询sql中where子句的JsonObject,
        JsonObject queryJSObj = new JsonObject();
        queryJSObj.put("warehouse_id", warehouse_id);
        bodyJSObj.put("aggregation_id",aggregation_id);
        queryJSObj.put("real_supply_chain_goods_settle_package_spec", supply_chain_goods_settle_package_spec);
        queryJSObj.put("goods_batch_code", goods_batch_code);
        queryJSObj.put("is_delete", 0);
        queryJSObj.put("generate_date", generate_date);
        this.commonRepository.getCon(client).onSuccess(conn -> {
            //获取满足条件的记录总数
            String count_sql = this.commonRepository.createCountSql(queryJSObj, "good_damage_supplier_subsidy_detail");
            Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "get_good_damage_supplier_subsidy_detail");
            String sql = createSelectSqlForGoodDamageSupplierSubsidyDetail(queryJSObj, query_offset, query_limit);
            Future<JsonArray> future =  this.commonRepository.queryJsonArray(conn, sql, "getRecordsForGoodDamageSupplierSubsidyDetail");

            CompositeFuture.join(Arrays.asList(getTotalFuture,future)).onComplete(ar2 -> {
                if (ar2.succeeded()) {
                    int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                    JsonObject dataJSObj = new JsonObject();
                    JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(1);
                    dataJSObj.put("total", total_records).put("records",recordsJSArray);
                    responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                    promise.complete(responseJSObj);
                } else {
                    logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                    promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getGoodDamageSupplierSubsidyDetailRecords", "获取商品折损供应商补贴单明细失败"));
                }
            });
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getGoodDamageSupplierSubsidyDetailRecords", "数据库连接失败")));
        return promise.future();
    }

    /**
     * 创建查询符合搜索条件记录的sql语句  查看商品折损供应商补贴单明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForGoodDamageSupplierSubsidyDetail(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("site_id"), new CustomSql("(select site_name from site where is_delete=0 and id=site_id ) as site_name "),
                        new CustomSql("warehouse_id"), new CustomSql("(select w.warehouse_name from warehouse w where w.is_delete=0 and w.id=warehouse_id ) as warehouse_name "),
                        new CustomSql("goods_gather_area_id"), new CustomSql("(select gga.goods_gather_area_name from goods_gather_area gga  where gga.is_delete=0 and gga.id=goods_gather_area_id ) as goods_gather_area_name "),
                        new CustomSql("supplier_id"), new CustomSql("(select si.supplier_name from supplier_info si where si.is_delete=0 and si.id=supplier_id ) as supplier_name "),
                        new CustomSql("good_damage_supplier_subsidy_code"), new CustomSql("generate_date"), new CustomSql("aggregation_id"),
                        new CustomSql("(select ag.aggregation_name from aggregation_goods ag where ag.is_delete=0 and ag.id=aggregation_id ) as aggregation_name"),
                        new CustomSql("supply_chain_goods_id"), new CustomSql("(select scg.supply_chain_goods_name from supply_chain_goods scg where scg.is_delete=0 and scg.id=supply_chain_goods_id ) as supply_chain_goods_name "),
                        new CustomSql("goods_batch_code"), new CustomSql("real_supply_chain_goods_grade_id"),
                        new CustomSql("(select sg.grade_name from supplier_grade sg where sg.is_delete=0 and sg.id=real_supply_chain_goods_grade_id ) as grade_name "),
                        new CustomSql("real_supply_chain_goods_settle_package_spec"), new CustomSql("real_deal_price"), new CustomSql("supplier_pickup_goods_inspect_accept_code"), new CustomSql("retrun_warehouse_number"), new CustomSql("retrun_warehouse_weight"),
                        new CustomSql("damage_number"), new CustomSql("damage_weight"), new CustomSql("good_damage_supplier_subsidy_price"),
                        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
                .addCustomFromTable("good_damage_supplier_subsidy_detail");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }

    /**
     * 查看加工报损单明细 根据传入的查询参数获取满足条件的记录
     *
     * @param client
     * @param config
     * @param bodyJSObj
     * @return
     */
    public Future<JsonObject> getRecordsForGoodsProcessLoss(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        //最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        // 验证必须传的数值字段
        List<String> numeric_fields = Arrays.asList("warehouse_id", "aggregation_id", "page_size", "page_index");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }

        //从bodyJson中获取数据   各字段注释请参看数据库设计文档中数据表supplier_goods_process_loss的说明\
        String warehouse_id = bodyJSObj.getString("warehouse_id");
        String aggregation_id = bodyJSObj.getString("aggregation_id");
        String loss_date = bodyJSObj.getString("loss_date");

        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;
        //用于传递给构建sql的JsonObject
        JsonObject queryJSObj = new JsonObject();


        queryJSObj.put("warehouse_id", warehouse_id);
        queryJSObj.put("aggregation_id", aggregation_id);
        queryJSObj.put("is_delete", 0);
        queryJSObj.put("loss_date", loss_date);
        this.commonRepository.getCon(client).onSuccess(conn -> {
            //先获取供应商商品加工报损单
            String sql = createSelectSqlForSupplierGoodsProcessLossDetail(queryJSObj, query_offset, query_limit);
            String sql2 = createSelectSqlForSupplierGoodsProcessLossDetail2(queryJSObj, query_offset, query_limit);
            //获取满足条件的记录总数
            String count_sql = this.commonRepository.createCountSql(queryJSObj, "supplier_goods_process_loss");
            String count_sql2 = this.commonRepository.createCountSql(queryJSObj, "sales_supplier_goods_process_loss");
            Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "getRecordsForGoodsProcessLoss");
            Future<Integer> getTotalFuture2 = this.commonRepository.getRecordsCount(conn, count_sql2, "getRecordsForSalesGoodsProcessLoss");
            Future<JsonArray> future =  this.commonRepository.queryJsonArray(conn, sql, "getRecordsForGoodsProcessLoss");
            Future<JsonArray> future2 =  this.commonRepository.queryJsonArray(conn, sql2, "getRecordsForSalesGoodsProcessLoss");
            CompositeFuture.join(Arrays.asList(getTotalFuture, future ,getTotalFuture2,future2)).onComplete(ar2 -> {
                if (ar2.succeeded()) {
                    int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                    int total_records2 = Integer.valueOf(ar2.result().list().get(2).toString());
                    if (total_records!=0){
                        JsonObject dataJSObj = new JsonObject();
                        JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(1);
                        dataJSObj.put("total", total_records).put("records",recordsJSArray);
                        responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                        promise.complete(responseJSObj);
                    }else if (total_records2!=0){
                        JsonObject dataJSObj = new JsonObject();
                        JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(3);
                        dataJSObj.put("total", total_records2).put("records",recordsJSArray);
                        responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                        promise.complete(responseJSObj);
                    }else {
                        JsonObject dataJSObj = new JsonObject();
                        dataJSObj.put("total", 0).put("records","");
                        responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                        promise.complete(responseJSObj);
                    }
                } else {
                    logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                    promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getRecordsForGoodsProcessLoss", "获取供应商商品加工报损单失败"));
                }
            });
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getRecordsForGoodsProcessLoss", "数据库连接失败")));
        return promise.future();
    }

    /**
     * 创建查询符合搜索条件记录的sql语句——查看加工报损单明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForSupplierGoodsProcessLossDetail(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("warehouse_id"), new CustomSql("supplier_goods_process_loss_code"), new CustomSql("partner_loss_ratio_id"),
                        new CustomSql("(select plr.loss_type_name from partner_loss_ratio plr where plr.is_delete=0 " +
                                "and plr.id=partner_loss_ratio_id ) as loss_type_name"),
                        new CustomSql("(select spgiad.goods_batch_code " +
                                "from supplier_pickup_goods_inspect_accept_detail spgiad " +
                                "where spgiad.is_delete=0 " +
                                "and spgiad.id=supplier_pickup_goods_inspect_accept_detail_id ) as goods_batch_code "),
                        new CustomSql("(select w.warehouse_name from warehouse w where w.is_delete=0 and w.id=warehouse_id ) as warehouse_name "),
                        new CustomSql("(select scg.supply_chain_goods_name from supply_chain_goods scg  where scg.is_delete=0 " +
                                "and scg.id= supply_chain_goods_id ) as supply_chain_goods_name "), new CustomSql("supply_chain_goods_id"), new CustomSql("loss_weight"), new CustomSql("loss_number"),
                        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
                .addCustomFromTable("supplier_goods_process_loss");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }

    /**
     * 创建查询符合搜索条件记录的sql语句——查看加工报损单明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForSupplierGoodsProcessLossDetail2(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("warehouse_id"), new CustomSql("sales_supplier_goods_process_loss_code"), new CustomSql("partner_loss_ratio_id"),
                        new CustomSql("(select plr.loss_type_name from partner_loss_ratio plr where plr.is_delete=0 and plr.id=partner_loss_ratio_id ) as loss_type_name"),
                        new CustomSql("(select spgiad.goods_batch_code " +
                                "from sales_supplier_pickup_goods_inspect_accept_detail spgiad " +
                                "where spgiad.is_delete=0 " +
                                "and spgiad.id=sales_supplier_pickup_goods_inspect_accept_detail_id ) as goods_batch_code "),
                        new CustomSql("(select w.warehouse_name from warehouse w where w.is_delete=0 and w.id=warehouse_id ) as warehouse_name "),
                        new CustomSql("(select scg.supply_chain_goods_name from supply_chain_goods scg  where scg.is_delete=0 " +
                                "and scg.id= supply_chain_goods_id ) as supply_chain_goods_name "), new CustomSql("supply_chain_goods_id"), new CustomSql("loss_weight"), new CustomSql("loss_number"),
                        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
                .addCustomFromTable("sales_supplier_goods_process_loss");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }


    /**
     * 创建查询符合搜索条件记录的sql语句——查看销售地区供应商商品加工报损单明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForSalesSupplierGoodsProcessLossDetail(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("warehouse_id"), new CustomSql("supplier_goods_process_loss_code"), new CustomSql("loss_type_name"),
        new CustomSql("goods_batch_code"), new CustomSql("supply_chain_goods_id"), new CustomSql("loss_weight"), new CustomSql("loss_number"),
        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
        .addCustomFromTable("sales_supplier_goods_process_loss");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }


    /**
     * 查看盘盈盘亏单明细 根据传入的查询参数获取满足条件的记录
     *
     * @param client
     * @param config
     * @param bodyJSObj
     * @return
     */
    public Future<JsonObject> getRecordsForGoodsProfitLoss(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        //最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        // 验证必须传的数值字段
        List<String> numeric_fields = Arrays.asList("warehouse_id", "aggregation_id", "page_size", "page_index");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }

        //从bodyJson中获取数据   各字段注释请参看数据库设计文档中数据表supplier_goods_profit_loss的说明\
        String warehouse_id = bodyJSObj.getString("warehouse_id");
        String aggregation_id = bodyJSObj.getString("aggregation_id");
        String inspect_stock_date = bodyJSObj.getString("inspect_stock_date");

        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;
        //用于传递给构建sql的JsonObject
        JsonObject queryJSObj = new JsonObject();

        //定义多个set，存储每一个 initial_batch_detail 中多个code
        Set<String> warehouse_id_list_set = new HashSet<String>();
        Set<String> supply_chain_goods_id_list_set = new HashSet<String>();

        queryJSObj.put("warehouse_id", warehouse_id);
        queryJSObj.put("aggregation_id", aggregation_id);
        queryJSObj.put("is_delete", 0);
        queryJSObj.put("inspect_stock_date", inspect_stock_date);
        this.commonRepository.getCon(client).onSuccess(conn -> {
            //先获取供应商商品盘盈盘亏单
            String sql = createSelectSqlForSupplierGoodsProfitLossDetail(queryJSObj, query_offset, query_limit);
            String sql2 = createSelectSqlForSalesSupplierGoodsProfitLossDetail(queryJSObj, query_offset, query_limit);
            //获取满足条件的记录总数
            String count_sql = this.commonRepository.createCountSql(queryJSObj, "supplier_goods_profit_loss");
            String count_sql2 = this.commonRepository.createCountSql(queryJSObj, "sales_supplier_goods_profit_loss");
            Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "getRecordsForGoodsProfitLoss");
            Future<Integer> getTotalFuture2 = this.commonRepository.getRecordsCount(conn, count_sql2, "getRecordsForSalesGoodsProfitLoss");
            Future<JsonArray> future =  this.commonRepository.queryJsonArray(conn, sql, "getRecordsForGoodsProfitLoss");
            Future<JsonArray> future2 =  this.commonRepository.queryJsonArray(conn, sql2, "getRecordsForSalesGoodsProfitLoss");
            CompositeFuture.join(Arrays.asList(getTotalFuture, future ,getTotalFuture2,future2)).onComplete(ar2 -> {
                if (ar2.succeeded()) {
                    int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                    int total_records2 = Integer.valueOf(ar2.result().list().get(2).toString());
                    if (total_records!=0){
                        JsonObject dataJSObj = new JsonObject();
                        JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(1);

                        for (int i = 0 ; i < recordsJSArray.size();i++){
                            JsonObject jsonObject =  recordsJSArray.getJsonObject(i);
                            double profit_loss_number = Double.parseDouble(jsonObject.getString("profit_loss_number"));
                            double real_settlement_package_spec = Double.parseDouble(jsonObject.getString("real_settlement_package_spec"));
                            BigDecimal bigDecimal1= new BigDecimal(profit_loss_number);
                            BigDecimal bigDecimal2= new BigDecimal(real_settlement_package_spec);
                            double weight = bigDecimal1.multiply(bigDecimal2).doubleValue();
                            jsonObject.put("all_weight",weight);

                        }
                        dataJSObj.put("total", total_records).put("records",recordsJSArray);
                        responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                        promise.complete(responseJSObj);
                    }else if (total_records2!=0){
                        JsonObject dataJSObj = new JsonObject();
                        JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(3);
                        for (int i = 0 ; i < recordsJSArray.size();i++){
                            JsonObject jsonObject =  recordsJSArray.getJsonObject(i);
                            double profit_loss_number = Double.parseDouble(jsonObject.getString("profit_loss_number"));
                            double real_settlement_package_spec = Double.parseDouble(jsonObject.getString("real_settlement_package_spec"));
                            BigDecimal bigDecimal1= new BigDecimal(profit_loss_number);
                            BigDecimal bigDecimal2= new BigDecimal(real_settlement_package_spec);
                            double weight = bigDecimal1.multiply(bigDecimal2).doubleValue();
                            jsonObject.put("all_weight",weight);

                        }
                        dataJSObj.put("total", total_records2).put("records",recordsJSArray);
                        responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                        promise.complete(responseJSObj);
                    }else {
                        JsonObject dataJSObj = new JsonObject();
                        dataJSObj.put("total", 0).put("records","");
                        responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                        promise.complete(responseJSObj);
                    }
                } else {
                    logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                    promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getRecordsForGoodsProfitLoss", "获取盘盈盘亏单明细失败"));
                }
            });


        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getRecordsForGoodsProfitLoss", "数据库连接失败")));
        return promise.future();
    }

    /**
     * 创建查询符合搜索条件记录的sql语句——查看盘盈盘亏单明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForSupplierGoodsProfitLossDetail(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("supplier_goods_profit_loss_code"), new CustomSql("warehouse_id"),
                        new CustomSql("(select w.warehouse_name from warehouse w where w.is_delete=0 and w.id=warehouse_id ) as warehouse_name "),
                        new CustomSql("(select scg.supply_chain_goods_name from supply_chain_goods scg  where scg.is_delete=0 " +
                                "and scg.id= supply_chain_goods_id ) as supply_chain_goods_name "), new CustomSql("supply_chain_goods_id"), new CustomSql("(select spppd.real_settlement_package_spec " +
                                "from supplier_pickup_plan_payment_detail spppd " +
                                "where spppd.is_delete=0 " +
                                "and spppd.id =( " +
                                "    select spgiad.supplier_pickup_plan_payment_detail_id " +
                                "    from supplier_pickup_goods_inspect_accept_detail spgiad " +
                                "    where spgiad.is_delete=0 " +
                                "      and spgiad.id =supplier_pickup_goods_inspect_accept_detail_id " +
                                "    ) )as real_settlement_package_spec"), new CustomSql("profit_loss_number"),
                        new CustomSql("(select spgiad.goods_batch_code " +
                                "from supplier_pickup_goods_inspect_accept_detail spgiad " +
                                "where spgiad.is_delete=0 " +
                                "and spgiad.id=supplier_pickup_goods_inspect_accept_detail_id ) as goods_batch_code "),
                        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
                .addCustomFromTable("supplier_goods_profit_loss");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }

    /**
     * 创建查询符合搜索条件记录的sql语句——查看销售地区供应商商品盘盈盘亏单明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForSalesSupplierGoodsProfitLossDetail(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("supplier_goods_profit_loss_code"), new CustomSql("warehouse_id"),
                        new CustomSql("(select w.warehouse_name from warehouse w where w.is_delete=0 and w.id=warehouse_id ) as warehouse_name "),
                        new CustomSql("(select scg.supply_chain_goods_name from supply_chain_goods scg  where scg.is_delete=0 " +
                                "and scg.id= supply_chain_goods_id ) as supply_chain_goods_name "), new CustomSql("supply_chain_goods_id"),
                        new CustomSql("(select spppd.real_settlement_package_spec " +
                                "from sales_pickup_plan_command spppd " +
                                "where spppd.is_delete=0 " +
                                "  and spppd.id =( " +
                                "    select spgiad.sales_pickup_plan_command_id " +
                                "    from sales_supplier_pickup_goods_inspect_accept_detail spgiad " +
                                "    where spgiad.is_delete=0 " +
                                "      and spgiad.id =sales_supplier_pickup_goods_inspect_accept_detail_id " +
                                ")) as real_settlement_package_spec"), new CustomSql("profit_loss_number"),
                        new CustomSql("(select spgiad.goods_batch_code " +
                                "from sales_supplier_pickup_goods_inspect_accept_detail spgiad " +
                                "where spgiad.is_delete=0 " +
                                "and spgiad.id=sales_supplier_pickup_goods_inspect_accept_detail_id ) as goods_batch_code "),
                        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
                .addCustomFromTable("sales_supplier_goods_profit_loss");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }


    /**
     * 查看采购退货单明细 根据传入的查询参数获取满足条件的记录
     *
     * @param client
     * @param config
     * @param bodyJSObj
     * @return
     */
    public Future<JsonObject> getRecordsForPurchaseReturnDetail(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        //最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        // 验证必须传的数值字段
        List<String> numeric_fields = Arrays.asList("warehouse_id", "aggregation_id","goods_batch_code", "page_size", "page_index");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }

        //从bodyJson中获取数据   各字段注释请参看数据库设计文档中数据表purchase_return_detail的说明\
        String warehouse_id = bodyJSObj.getString("warehouse_id");
        String aggregation_id = bodyJSObj.getString("aggregation_id");
        String goods_batch_code = bodyJSObj.getString("goods_batch_code");

        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;
        //用于传递给构建sql的JsonObject
        JsonObject queryJSObj = new JsonObject();

        //定义多个set，存储每一个 initial_batch_detail 中多个code
        Set<String> warehouse_id_list_set = new HashSet<String>();
        Set<String> supply_chain_goods_id_list_set = new HashSet<String>();
        Set<String> supplier_id_list_set = new HashSet<String>();
        //首先根据聚合商品名获取对应的供应链商品名
        ArrayList<String> supply_chain_goods_id_list = new ArrayList<>();
        supply_chain_goods_id_list.add("[");
        this.commonRepository.getCon(client).onSuccess(conn -> {
            //先根据聚合商品名获取对应的供应链商品名
            JsonObject queryJSObj1 = new JsonObject();
            queryJSObj1.put("aggregation_id", aggregation_id);
            String get_supply_chain_gooods_id_sql = createSelectSqlGetSupplyChainGooodsId(queryJSObj1, query_offset, query_limit);
            this.commonRepository.queryJsonArray(conn, get_supply_chain_gooods_id_sql, "getInitialBatchDetialRecords")
            .onSuccess(resJSArray1 -> {
                //        取出每条记录中含code字段的值放入对应的集合
                for (int i = 0; i < resJSArray1.size(); i++) {
                    JsonObject jsonObj = resJSArray1.getJsonObject(i);
                    supply_chain_goods_id_list.add(jsonObj.getString("supply_chain_goods_id"));
                }
                supply_chain_goods_id_list.add("]");
                //用于传递给构建sql的JsonObject
                queryJSObj.put("warehouse_id", warehouse_id);
                queryJSObj.put("supply_chain_goods_id", String.join(" ", supply_chain_goods_id_list));
                queryJSObj.put("goods_batch_code", goods_batch_code);

                String sql = createSelectSqlForPurchaseReturnDetail(queryJSObj, query_offset, query_limit);
                this.commonRepository.queryJsonArray(conn, sql, "getPurchaseReturnDetailRecords")
                .onSuccess(resJSArray -> { //resJSArray中就是获取到符合条件的数据
                    //        取出每条记录中含code字段的值放入对应的集合
                    for (int i = 0; i < resJSArray.size(); i++) {
                        JsonObject jsonObj = resJSArray.getJsonObject(i);
                        warehouse_id_list_set.add(jsonObj.getString("warehouse_id"));
                        supply_chain_goods_id_list_set.add(jsonObj.getString("supply_chain_goods_id"));
                        supplier_id_list_set.add(jsonObj.getString("supplier_id"));
                    }
                    //获取满足条件的记录总数
                    String count_sql = this.commonRepository.createCountSql(queryJSObj, "purchase_return_detail");
                    Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "get_purchase_return_detail");
                    //获取仓库编码和名称
                    String warehouse_sql = "select distinct id, warehouse_name from  warehouse where id = any(array[" +
                    String.join(",", warehouse_id_list_set) + "])";
                    Future<JsonObject> getWarehouseFuture = this.commonRepository.getCodeNamePairJsonObject(conn, warehouse_sql, "get_warehouse_name");
                    //获取供应链商品编码和名称
                    String supply_chain_goods_sql = "select distinct id, supply_chain_goods_name from  supply_chain_goods where id = any(array[" +
                    String.join(",", supply_chain_goods_id_list_set) + "])";
                    Future<JsonObject> getSupplyChainGoodsFuture = this.commonRepository.getCodeNamePairJsonObject(conn, supply_chain_goods_sql, "get_supply_chain_goods_name");
                    //获取供应商编码和名称
                    String supplier_sql = "select distinct id, supplier_name from supplier_info where id = any(array[" +
                    String.join(",", supplier_id_list_set) + "])";
                    Future<JsonObject> getSupplierFuture = this.commonRepository.getCodeNamePairJsonObject(conn, supplier_sql, "get_supplier");


                    CompositeFuture.join(Arrays.asList(getTotalFuture, getWarehouseFuture, getSupplyChainGoodsFuture, getSupplierFuture)).onComplete(ar2 -> {
                        if (ar2.succeeded()) {
                            int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                            JsonObject warehouse_info = new JsonObject();
                            JsonObject supply_chain_goods_info = new JsonObject();
                            JsonObject supplier_info = new JsonObject();

                            warehouse_info = (JsonObject) ar2.result().list().get(1);
                            supply_chain_goods_info = (JsonObject) ar2.result().list().get(2);
                            supplier_info = (JsonObject) ar2.result().list().get(3);
                            // 最后组装含有name的Json对象和数组
                            JsonArray recordsJSArray = new JsonArray();
                            for (int i = 0; i < resJSArray.size(); i++) {
                                JsonObject record_obj = resJSArray.getJsonObject(i);
                                record_obj.put("warehouse_name", warehouse_info.getString(record_obj.getString("warehouse_id")));
                                record_obj.put("supply_chain_goods_name", supply_chain_goods_info.getString(record_obj.getString("supply_chain_goods_id")));
                                record_obj.put("supplier_name", supplier_info.getString(record_obj.getString("supplier_id")));
                                recordsJSArray.add(record_obj);
                            }
                            JsonObject dataJSObj = new JsonObject();
                            dataJSObj.put("total", total_records).put("records", recordsJSArray);
                            responseJSObj.put("id", 0).put("msg", "获取记录成功").put("data", dataJSObj);
                            promise.complete(responseJSObj);
                        } else {
                            logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                            promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getPurchaseReturnDetailRecords", "获取采购退货单明细参数失败"));
                        }
                    });
                }).onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getPurchaseReturnDetailRecords", "获取采购退货单明细参数失败")));
            }).onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getSupplyChainGooodsIdRecords", "获取供应链商品参数失败")));
        }).
        onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getPurchaseReturnDetailRecords", "数据库连接失败")));
        return promise.future();
    }

    /**
     * 创建查询符合搜索条件记录的sql语句——查看采购退货单明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForPurchaseReturnDetail(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("purchase_return_code"), new CustomSql("goods_batch_code"),
        new CustomSql("supply_chain_goods_id"), new CustomSql("supplier_id"), new CustomSql("purchase_return_weight"), new CustomSql("purchase_return_number"),
        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
        .addCustomFromTable("purchase_return_detail");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }


    /**
     * 根据传入的查询参数获取满足条件的记录——查看商品内部赔偿单明细
     *
     * @param client    连接池对象
     * @param config    配置对象
     * @param bodyJSObj body参数对象
     * @return 满足条件的记录
     */
    public Future<JsonObject> getRecordsForGoodIndoorSubsidyDetail(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        //从bodyJson中获取传递的参数, 各参数字段含义 请参看 insertOrUpdate方法体中的注释
        /** 从bodyJson中获取传递的参数说明,适用于方法createSelectSql 和 getRecords
         */

        // 验证必须传的数值字段
        List<String> numeric_fields = Arrays.asList("warehouse_id", "aggregation_id","supply_chain_goods_settle_package_spec", "goods_batch_code", "page_size", "page_index");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }

        //从bodyJson中获取数据   各字段注释请参看数据库设计文档中数据表sales_supplier_goods_profit_loss的说明\
        String warehouse_id = bodyJSObj.getString("warehouse_id");
        String aggregation_id = bodyJSObj.getString("aggregation_id");
        String supply_chain_goods_settle_package_spec = bodyJSObj.getString("supply_chain_goods_settle_package_spec");
        String goods_batch_code = bodyJSObj.getString("goods_batch_code");
        String generate_date = bodyJSObj.getString("generate_date");

        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;
        // 传递给构建查询sql中where子句的JsonObject,
        JsonObject queryJSObj = new JsonObject();
        queryJSObj.put("warehouse_id", warehouse_id);
        queryJSObj.put("aggregation_id", aggregation_id);
        queryJSObj.put("real_supply_chain_goods_settle_package_spec", supply_chain_goods_settle_package_spec);
        queryJSObj.put("goods_batch_code", goods_batch_code);
        queryJSObj.put("generate_date", generate_date);
        queryJSObj.put("is_delete", 0);
        this.commonRepository.getCon(client).onSuccess(conn -> {
            //获取满足条件的记录总数
            String count_sql = this.commonRepository.createCountSql(queryJSObj, "good_indoor_subsidy_detail");
            Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "getRecordsForGoodIndoorSubsidyDetail");
            String sql = createSelectSqlForGoodIndoorSubsidyDetail(queryJSObj, query_offset, query_limit);
            Future<JsonArray> future =  this.commonRepository.queryJsonArray(conn, sql, "getRecordsForGoodIndoorSubsidyDetail");
            CompositeFuture.join(Arrays.asList(getTotalFuture,  future)).onComplete(ar2 -> {
                if (ar2.succeeded()) {
                    int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                    JsonObject dataJSObj = new JsonObject();
                    JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(1);
                    dataJSObj.put("total", total_records).put("records",recordsJSArray);
                    responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                    promise.complete(responseJSObj);
                } else {
                    logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                    promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getRecordsForGoodIndoorSubsidyDetail", "获取商品内部赔偿单明细失败"));
                }
            });
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getRecordsForGoodIndoorSubsidyDetail", "数据库连接失败")));
        return promise.future();
    }

    /**
     * 创建查询符合搜索条件记录的sql语句  查看商品内部赔偿单明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForGoodIndoorSubsidyDetail(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("site_id"),new CustomSql("(select site_name from site where is_delete=0 and id=site_id ) as site_name "), new CustomSql("warehouse_id"),new CustomSql("(select w.warehouse_name from warehouse w where w.is_delete=0 and w.id=warehouse_id ) as warehouse_name "),
                        new CustomSql("good_indoor_subsidy_code"), new CustomSql("generate_date"), new CustomSql("aggregation_id"),new CustomSql("(select ag.aggregation_name from aggregation_goods ag where ag.is_delete=0 and ag.id=aggregation_id ) as aggregation_name"), new CustomSql("supply_chain_goods_id"),
                        new CustomSql("(select scg.supply_chain_goods_name from supply_chain_goods scg where scg.is_delete=0 and scg.id=supply_chain_goods_id ) as supply_chain_goods_name "),new CustomSql("goods_batch_code"),
                        new CustomSql("real_supply_chain_goods_grade_id"),new CustomSql("(select sg.grade_name from supplier_grade sg where sg.is_delete=0 and sg.id=real_supply_chain_goods_grade_id ) as grade_name "), new CustomSql("real_supply_chain_goods_settle_package_spec"), new CustomSql("indoor_subsidy_number"), new CustomSql("indoor_subsidy_weight"),
                        new CustomSql("indoor_subsidy_price"), new CustomSql("land_price"),
                        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
                .addCustomFromTable("good_indoor_subsidy_detail");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }


    /**
     * 根据传入的查询参数获取满足条件的记录——查看销售投诉赔偿单明细
     *
     * @param client    连接池对象
     * @param config    配置对象
     * @param bodyJSObj body参数对象
     * @return 满足条件的记录
     */
    public Future<JsonObject> getRecordsForSalesComplaintsSubsidyDetail(PgPool client, JsonObject config, JsonObject bodyJSObj) {
        Promise<JsonObject> promise = Promise.promise();
        // 最后返回的JsonObject对象
        JsonObject responseJSObj = new JsonObject();
        //从bodyJson中获取传递的参数, 各参数字段含义 请参看 insertOrUpdate方法体中的注释
        /** 从bodyJson中获取传递的参数说明,适用于方法createSelectSql 和 getRecords
         */

        // 验证必须传的数值字段
        List<String> numeric_fields = Arrays.asList("aggregation_id","warehouse_id", "supply_chain_goods_settle_package_spec", "goods_batch_code", "page_size", "page_index");
        JsonObject numeric = this.commonRepository.verifyAllParamNumeric(bodyJSObj, numeric_fields);
        if (numeric != null) {
            promise.complete(numeric);
            return promise.future();
        }

        //从bodyJson中获取数据   各字段注释请参看数据库设计文档中数据表sales_supplier_goods_profit_loss的说明\
        String warehouse_id = bodyJSObj.getString("warehouse_id");
        String aggregation_id = bodyJSObj.getString("aggregation_id");
        String supply_chain_goods_settle_package_spec = bodyJSObj.getString("supply_chain_goods_settle_package_spec");
        String goods_batch_code = bodyJSObj.getString("goods_batch_code");
        String trade_date = bodyJSObj.getString("trade_date");

        int page_size = Integer.valueOf(bodyJSObj.getString("page_size"));
        int page_index = Integer.valueOf(bodyJSObj.getString("page_index"));
        int query_offset = (page_index - 1) * page_size;
        int query_limit = page_size;
        //首先根据聚合商品名获取对应的供应链商品名
        Set<Integer> supplys= new HashSet<>();
        this.commonRepository.getCon(client).onSuccess(conn -> {
            //获取满足条件的记录总数
            JsonObject queryJSObj1 = new JsonObject();
            queryJSObj1.put("aggregation_id", aggregation_id);
            String get_supply_chain_gooods_id_sql = createSelectSqlGetSupplyChainGooodsId(queryJSObj1, query_offset, query_limit);
            this.commonRepository.queryJsonArray(conn, get_supply_chain_gooods_id_sql, "getInitialBatchDetialRecords")
                    .onSuccess(resJSArray1 -> {
                        //        取出每条记录中含code字段的值放入对应的集合
                        if (resJSArray1.size()==0){
                            JsonObject dataJSObj = new JsonObject();
                            dataJSObj.put("total", 0).put("records","");
                            responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                            promise.complete(responseJSObj);
                        }else {
                            for (int i = 0; i < resJSArray1.size(); i++) {
                                JsonObject jsonObj = resJSArray1.getJsonObject(i);
                                Integer a = Integer.valueOf(jsonObj.getString("supply_chain_goods_id"));
                                supplys.add(a);
                            }
                            // 传递给构建查询sql中where子句的JsonObject,
                            JsonObject queryJSObj = new JsonObject();
                            queryJSObj.put("warehouse_id", warehouse_id);
                            String supply_str="";
                            Integer[] array = supplys.toArray(new Integer[supplys.size()]);
                            for (int i = 0 ; i < array.length ; i ++){
                                if (i==0){
                                    supply_str=supply_str+array[i];
                                }else {
                                    supply_str=supply_str+","+array[i];
                                }
                            }
                            queryJSObj.put("supply_chain_goods_id", supply_str);
                            queryJSObj.put("supply_chain_goods_settle_package_spec", supply_chain_goods_settle_package_spec);
                            queryJSObj.put("goods_batch_code", goods_batch_code);
                            queryJSObj.put("trade_date", trade_date);
                            queryJSObj.put("is_delete", 0);
                            String count_sql = this.commonRepository.createCountSql(queryJSObj, "sales_complaints_subsidy_detail");
                            Future<Integer> getTotalFuture = this.commonRepository.getRecordsCount(conn, count_sql, "getRecordsForSalesComplaintsSubsidyDetail");
                            String sql = createSelectSqlForSalesComplaintsSubsidyDetail(queryJSObj, query_offset, query_limit);
                            Future<JsonArray> future =  this.commonRepository.queryJsonArray(conn, sql, "getRecordsForSalesComplaintsSubsidyDetail");
                            CompositeFuture.join(Arrays.asList(getTotalFuture, future)).onComplete(ar2 -> {
                                if (ar2.succeeded()) {
                                    int total_records = Integer.valueOf(ar2.result().list().get(0).toString());
                                    JsonObject dataJSObj = new JsonObject();
                                    JsonArray recordsJSArray=(JsonArray)ar2.result().list().get(1);
                                    dataJSObj.put("total", total_records).put("records",recordsJSArray);
                                    responseJSObj.put("code", 200).put("msg", "获取记录成功").put("data", dataJSObj);
                                    promise.complete(responseJSObj);
                                } else {
                                    logger.info("获取记录数据发生错误: {}", ar2.cause().getMessage());
                                    promise.complete(CommonUtils.createExceptionJsonObject(ar2, "getRecordsForSalesComplaintsSubsidyDetail", "获取销售投诉赔偿单明细失败"));
                                }
                            });

                        }


                    }).onFailure(err -> promise.complete(CommonUtils.createExceptionJsonObject(err, "getRecordsForSalesReturnWarehouseDetial", "获取供应链商品参数失败")));
        }).onFailure(err -> promise.fail(CommonUtils.createJsonObject(err, "getRecordsForSalesComplaintsSubsidyDetail", "数据库连接失败")));
        return promise.future();
    }

    /**
     * 创建查询符合搜索条件记录的sql语句  查看销售投诉赔偿单明细
     *
     * @param queryJS      body参数对象
     * @param query_offset 页面偏移
     * @param query_limit  页面记录数
     * @return 查询符合搜索条件的sql语句
     */
    public String createSelectSqlForSalesComplaintsSubsidyDetail(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("id"), new CustomSql("trade_date"), new CustomSql("warehouse_id"),new CustomSql("(select w.warehouse_name from warehouse w where w.is_delete=0 and w.id=warehouse_id ) as warehouse_name "),
                        new CustomSql("sales_complaints_subsidy_code"), new CustomSql("buyer_name"), new CustomSql("buyer_id"), new CustomSql("order_code"), new CustomSql("list_goods_id"),new CustomSql("(select lg.list_goods_name from list_goods lg where lg.is_delete=0 and lg.id=list_goods_id ) as list_goods_name "),
                        new CustomSql("list_package_spec"), new CustomSql("sales_package_spec_id"),new CustomSql("(select spst.package_spec_name from sales_package_spec spst " +
                                " where spst.is_delete=0 and spst.id=sales_package_spec_id ) type_name "), new CustomSql("supply_chain_goods_id"), new CustomSql("(select scg.supply_chain_goods_name from supply_chain_goods scg " +
                                " where scg.is_delete=0  and scg.id=supply_chain_goods_id) as supply_chain_goods_name "),
                        new CustomSql("supply_chain_goods_settle_package_spec"),
                        new CustomSql("order_number"), new CustomSql("order_weight"), new CustomSql("delivery_date"), new CustomSql("shift_delivery_plan_completion_code"), new CustomSql("goods_batch_code"), new CustomSql("sales_complaints_subsidy_describe"),
                        new CustomSql("sales_complaints_subsidy_price"), new CustomSql("site_id"),new CustomSql("(select site_name from site where is_delete=0 and id=site_id ) as site_name "),
                        new CustomSql("is_delete"), new CustomSql("create_user"), new CustomSql("update_user"), new CustomSql("create_time"), new CustomSql("update_time"))
                .addCustomFromTable("sales_complaints_subsidy_detail");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }


    private String createSelectSqlGetSupplyChainGooodsId(JsonObject queryJS, int query_offset, int query_limit) {
        SelectQuery sq = new SelectQuery();
        sq.addCustomColumns(new CustomSql("supply_chain_goods_id")).addCustomFromTable("goods_parameter");
        Iterator<String> iter = queryJS.fieldNames().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            try {
                String value = queryJS.getString(key);
                if (!value.equals("") && !value.equals("[]") && !value.equals("[all]")) {
                    String firstChar = value.substring(0, 1);
                    if (firstChar.equals("["))
                        value = value.substring(1, value.length() - 1);
                    value = value.replace(", ", "','");
                    // 添加where  InCondtion 子句
                    sq.getWhereClause().addCustomCondition(new InCondition(new CustomSql(key), value));
                }
            } catch (Exception e) {
                logger.info("createSql, 发生错误,错误原因: ", e.getMessage());
            }
        }
        return sq.validate() + " order by id desc limit " + query_limit + " offset " + query_offset;
    }

}